package DB;

import Model.Point;
import Model.Scenario;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * PointManager manages database access operations related to Point class
 * @author V3nom
 */
public class PointManager implements IFPointManager {

    //Attribute constants
    public static final String ALIAS = "alias";
    public static final String CRANE_POS_X = "cranePosX";
    public static final String CRANE_POS_Y = "cranePosY";
    public static final String POINT_ID = "pointId";
    public static final String POS_X = "posX";
    public static final String POS_Y = "posY";
    public static final String POS_Z = "posZ";
    public static final String SERIAL_NUMBER = "serialNumber";
    public static final String SLIDE_POS = "slidePos";
    public static final String TILT_POS = "tiltPos";
    public static final String TIME_FROM_START = "timeFromStart";
    //SQL Scripts
    private static final String GET_ALL_POINTS = "SELECT * FROM point WHERE"
            + " scenarioId = ?";
    private static final String SAVE_POINT = "INSERT INTO point"
            + " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
    private static final String UPDATE_POINT = "UPDATE point SET alias=?, timeFromStart=?, serialNumber=?, scenarioId=?,"
            + "posX=?, posY=?, posZ=?, tiltPos=?, slidePos=?, cranePosX=?, cranePosY=? WHERE pointId=?";
    private static final String DELETE_POINT = "DELETE FROM point WHERE"
            + " pointId = ?";
    private static final String GET_POINT_ID = "SELECT MAX(pointId) from point";
    private static final String DELETE_POINTS = "DELETE FROM point WHERE scenarioId=?";
    //SQL connection object
    private Connection connection;
    //Prepared statements
    private PreparedStatement psSavePoint;
    private PreparedStatement psGetAllPoints;
    private PreparedStatement psUpdatePoint;
    private PreparedStatement psDeletePoint;
    private PreparedStatement psGetId;
    private PreparedStatement psDeletePoints;
    //Camera manager
    private IFCameraManager cameraManager;

    public PointManager() {
        connection = DBConnector.getInstance().getDBConnection();
        initialiseStatements();
        cameraManager = new CameraManager();
    }

    /**
     * Retrieves all points from the database by scenarioId, which is extracted
     * from passed parameter
     * @param scenario
     * @return ArrayList<Point>
     * @throws IllegalArgumentException 
     */
    @Override
    public ArrayList<Point> getAllPoints(Scenario scenario) throws IllegalArgumentException {
        ArrayList<Point> allPoints = new ArrayList<Point>();
        Point currentPoint;
        try {
            psGetAllPoints.setInt(1, scenario.getScenarioId());
            ResultSet results = psGetAllPoints.executeQuery();

            while (results.next()) {
                currentPoint = new Point();

                currentPoint.setPointId(results.getInt(POINT_ID));
                currentPoint.setAlias(results.getString(ALIAS));
                currentPoint.setTimeFromStart(results.getInt(TIME_FROM_START));
                currentPoint.setPosX(results.getInt(POS_X));
                currentPoint.setCamera(cameraManager.getCamera(results.getInt(SERIAL_NUMBER)));
                currentPoint.setPosY(results.getInt(POS_Y));
                currentPoint.setPosZ(results.getInt(POS_Z));
                currentPoint.setTiltPos(results.getInt(TILT_POS));
                currentPoint.setSlidePos(results.getInt(SLIDE_POS));
                currentPoint.setCranePosX(results.getInt(CRANE_POS_X));
                currentPoint.setCranePosY(results.getInt(CRANE_POS_Y));

                allPoints.add(currentPoint);
            }

            results.close();
        } catch (SQLException ex) {
            System.err.println(ex);
        }
        return allPoints;
    }

    /**
     * Saves point to the database, if point already has pointId,
     * then updates it
     * @param point
     * @param scenario
     * @throws IllegalArgumentException 
     */
    @Override
    public void savePoint(Point point, Scenario scenario) throws IllegalArgumentException {
        PreparedStatement toExecute;
        int pointId = point.getPointId();
        try {
            if (pointId == 0) {
                toExecute = psSavePoint;
            } else {
                toExecute = psUpdatePoint;
                toExecute.setInt(12, pointId);
            }

            toExecute.setString(1, point.getAlias());
            toExecute.setInt(2, point.getTimeFromStart());
            toExecute.setInt(3, point.getCamera().getSerialNumber());
            toExecute.setInt(4, scenario.getScenarioId());
            toExecute.setInt(5, point.getPosX());
            toExecute.setInt(6, point.getPosY());
            toExecute.setInt(7, point.getPosZ());
            toExecute.setInt(8, point.getTiltPos());
            toExecute.setInt(9, point.getSlidePos());
            toExecute.setInt(10, point.getCranePosX());
            toExecute.setInt(11, point.getCranePosY());
            int resultCode = toExecute.executeUpdate();

            if (pointId == 0) {
                point.setPointId(getID());
            } else if (resultCode == 0) {
                throw new IllegalArgumentException("Failed to save point");
            }
        } catch (SQLException ex) {
            System.err.println(ex);
        }


    }

    /**
     * Deletes point from the database
     * @param point
     * @throws IllegalArgumentException 
     */
    @Override
    public void deletePoint(Point point) throws IllegalArgumentException {
        try {
            psDeletePoint.setInt(1, point.getPointId());
            if (psDeletePoint.executeUpdate() == 0) {
                throw new IllegalArgumentException("Point deletion failed");
            }
        } catch (SQLException ex) {
            System.err.println(ex);
        }
    }

    /**
     * Saves all points from scenario to the databse
     * @param scenario
     * @throws IllegalArgumentException 
     */
    @Override
    public void savePoints(Scenario scenario) throws IllegalArgumentException {
        for (Point point : scenario.getPoints()) {
            savePoint(point, scenario);
        }
    }

    /**
     * Initialisation of prepared statements
     */
    private void initialiseStatements() {
        try {
            psGetAllPoints = connection.prepareStatement(GET_ALL_POINTS);
            psSavePoint = connection.prepareStatement(SAVE_POINT);
            psUpdatePoint = connection.prepareStatement(UPDATE_POINT);
            psDeletePoint = connection.prepareStatement(DELETE_POINT);
            psGetId = connection.prepareStatement(GET_POINT_ID);
            psDeletePoints = connection.prepareStatement(DELETE_POINTS);
        } catch (SQLException ex) {
            System.err.println(ex);
        }
    }

    /**
     * Gets id for latest point created
     * @return pointId
     */
    private int getID() {
        int pointId = 0;
        try {
            ResultSet result = psGetId.executeQuery();
            while (result.next()) {
                pointId = result.getInt(1);
            }
        } catch (SQLException ex) {
            System.err.println(ex);
        }
        return pointId;
    }

    /**
     * Deletes all points from scenario
     * @param scenario
     * @throws IllegalArgumentException 
     */
    @Override
    public void deletePoints(Scenario scenario) throws IllegalArgumentException {
        try {
            psDeletePoints.setInt(1, scenario.getScenarioId());
            psDeletePoints.executeUpdate();
        } catch (SQLException ex) {
            System.out.println(ex);
        }
    }
}
